/*
 * Local Projects Database
 * (c) 2003 by eRomania Gateway
 * Created on Jan 15, 2004
 *
 */
package ro.gateway.aida.usr.messaging;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.DBWrapable;
import ro.gateway.aida.db.PersistenceToken;



/**
 * MessageDB
 * @author Mihai Postelnicu <p>
 *  mihai@ro-gateway.org
 */
public class ProjectFilterDB extends DBPersistenceManager implements DBWrapable
{
      private ProjectFilterDB( PersistenceToken token )
      {
            super( token );
      }

      public static ProjectFilterDB getManager( PersistenceToken token )
      {
            return new ProjectFilterDB( token );
      }

      public void synchronize( Object o ) throws Exception
      {
            ProjectFilter m = ( ProjectFilter ) o;

            if ( m.getId() == -1 )
                  add( m );
            else
                  update( m );
      }

      public void delete( Object o ) throws Exception
      {
            ProjectFilter m = ( ProjectFilter ) o;
            if ( m.getId() != -1 ) deleteById( o );
            if ( m.getAlerterId() != -1 ) deleteByAlerterId( o );

      }

      public void deleteById( Object o ) throws Exception
      {
            ProjectFilter m = ( ProjectFilter ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE );
                  ps.setLong( 1, m.getId() );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      public void deleteByAlerterId( Object o ) throws Exception
      {
            ProjectFilter m = ( ProjectFilter ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE_BY_ALERTER_ID );

                  ps.setLong( 1, m.getAlerterId() );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }


      protected void update( Object o ) throws Exception
      {
            ProjectFilter m = ( ProjectFilter ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( UPDATE );


                  ps.setInt( 1, m.getSector() );
                  ps.setString( 2, m.getTitle() );
                  ps.setString( 3, m.getDescription() );
                  ps.setLong( 4, m.getFundingOrg() );
                  ps.setInt( 5, m.getStat() );
                  ps.setLong( 6, m.getDonorOrg() );
                  ps.setLong( 7, m.getContractingOrg() );
                  ps.setLong( 8, m.getBeneficiaryOrg() );
                  ps.setLong( 9, m.getInvolvedPerson() );
                  ps.setString( 10, m.getKeywords() );
                  ps.setLong( 11, m.getTriggeredUser() );
                  ps.setLong( 12, m.getTriggeredGroup() );
                  ps.setString( 13, m.getCountry_iso3() );

                  ps.setLong( 14, m.getId() );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      public Object getById( Object o ) throws Exception
      {
            ProjectFilter t = ( ProjectFilter ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET );
                  ps.setLong( 1, t.getId() );

                  ResultSet rs = ps.executeQuery();

                  if ( rs.next() )
                  {
                        t.setAlerterId( rs.getLong( 1 ) );
                        t.setSector( rs.getInt( 2 ) );
                        t.setTitle( rs.getString( 3 ) );
                        t.setDescription( rs.getString( 4 ) );
                        t.setFundingOrg( rs.getLong( 5 ) );
                        t.setStat( rs.getInt( 6 ) );
                        t.setDonorOrg( rs.getLong( 7 ) );
                        t.setContractingOrg( rs.getLong( "corg" ) );
                        t.setBeneficiaryOrg( rs.getLong( "borg" ) );
                        t.setInvolvedPerson( rs.getLong( "ipers" ) );
                        t.setKeywords( rs.getString( "keyw" ) );
                        t.setTriggeredUser( rs.getLong( "tracked_owner" ) );
                        t.setTriggeredGroup( rs.getLong( "tracked_group" ) );
                        t.setCountry_iso3( rs.getString( "country_iso3" ) );
                  }
                  else
                        t.setId( -1 );

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return t;
      }

      protected void add( Object o ) throws Exception
      {
            ProjectFilter m = ( ProjectFilter ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( ADD );

                  ps.setLong( 1, m.getAlerterId() );
                  ps.setInt( 2, m.getSector() );
                  ps.setString( 3, m.getTitle() );
                  ps.setString( 4, m.getDescription() );
                  ps.setLong( 5, m.getFundingOrg() );
                  ps.setInt( 6, m.getStat() );
                  ps.setLong( 7, m.getDonorOrg() );
                  ps.setLong( 8, m.getContractingOrg() );
                  ps.setLong( 9, m.getBeneficiaryOrg() );
                  ps.setLong( 10, m.getInvolvedPerson() );
                  ps.setString( 11, m.getKeywords() );
                  ps.setLong( 12, m.getTriggeredUser() );
                  ps.setLong( 13, m.getTriggeredGroup() );
                  ps.setString( 14, m.getCountry_iso3() );


                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      public Object get( Object o ) throws Exception
      {
            ProjectFilter m = ( ProjectFilter ) o;

            if ( m.getId() != -1 )
                  return getById( o );

            if ( m.getAlerterId() != -1 )
                  return getForAlerter( m );

            return getAll();
      }

      protected List getForAlerter( ProjectFilter m ) throws Exception
      {
            Connection con = getConnection();
            ArrayList ret = new ArrayList();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_FOR_ALERTER );
                  ps.setLong( 1, m.getAlerterId() );

                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        ProjectFilter t = new ProjectFilter( rs.getLong( 1 ),
                              m.getAlerterId() );
                        t.setSector( rs.getInt( 2 ) );
                        t.setTitle( rs.getString( 3 ) );
                        t.setDescription( rs.getString( 4 ) );
                        t.setFundingOrg( rs.getLong( 5 ) );
                        t.setStat( rs.getInt( 6 ) );
                        t.setDonorOrg( rs.getLong( 7 ) );
                        t.setContractingOrg( rs.getLong( "corg" ) );
                        t.setBeneficiaryOrg( rs.getLong( "borg" ) );
                        t.setInvolvedPerson( rs.getLong( "ipers" ) );
                        t.setKeywords( rs.getString( "keyw" ) );
                        t.setTriggeredUser( rs.getLong( "tracked_owner" ) );
                        t.setTriggeredGroup( rs.getLong( "tracked_group" ) );
                        t.setCountry_iso3( rs.getString( "country_iso3" ) );
                        ret.add( t );
                  }

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return ret;
      }

      protected List getAll() throws Exception
      {
            Connection con = getConnection();
            ArrayList ret = new ArrayList();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_ALL );

                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        ProjectFilter t = new ProjectFilter( rs.getLong( 1 ), rs.getLong( "alerter_id" ) );
                        t.setSector( rs.getInt( 2 ) );
                        t.setTitle( rs.getString( 3 ) );
                        t.setDescription( rs.getString( 4 ) );
                        t.setFundingOrg( rs.getLong( 5 ) );
                        t.setStat( rs.getInt( 6 ) );
                        t.setDonorOrg( rs.getLong( 7 ) );
                        t.setContractingOrg( rs.getLong( "corg" ) );
                        t.setBeneficiaryOrg( rs.getLong( "borg" ) );
                        t.setInvolvedPerson( rs.getLong( "ipers" ) );
                        t.setKeywords( rs.getString( "keyw" ) );
                        t.setTriggeredUser( rs.getLong( "tracked_owner" ) );
                        t.setTriggeredGroup( rs.getLong( "tracked_group" ) );
                        t.setCountry_iso3( rs.getString( "country_iso3" ) );
                        ret.add( t );
                  }

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return ret;
      }

      public static final String ADD = "INSERT INTO " +
            "prj_filters (alerter_id,sector,title,description,forg,stat,dorg, " +
            "corg,borg,ipers,keyw,tracked_owner,tracked_group,country_iso3) " +
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
      public static final String UPDATE =
            "UPDATE prj_filters SET sector=?,title=?,description=?,forg=?,stat=?," +
            "dorg=?,corg=?,borg=?,ipers=?,keyw=?,tracked_owner=?,tracked_group=?,country_iso3=? WHERE id=?";
      public static final String GET =
            "SELECT alerter_id,sector,title,description,forg,stat,dorg,corg,borg," +
            "ipers,keyw,tracked_owner,tracked_group,country_iso3 FROM prj_filters WHERE id=?";
      public static final String GET_FOR_ALERTER =
            "SELECT id,sector,title,description,forg,stat,dorg,corg,borg," +
            "ipers,keyw,tracked_owner,tracked_group,country_iso3 FROM prj_filters WHERE alerter_id=?";
      public static final String GET_ALL =
            "SELECT id,sector,title,description,forg,stat,dorg,corg,borg," +
            "ipers,keyw,tracked_owner,tracked_group,country_iso3,alerter_id FROM prj_filters";


      public static final String DELETE = "DELETE FROM prj_filters WHERE id=?";
      public static final String DELETE_BY_ALERTER_ID = "DELETE FROM prj_filters WHERE alerter_id=?";
}
